import { neon } from "@neondatabase/serverless"

// Create a SQL client with the Neon connection string
// Try different environment variable names that might be available
const getDatabaseUrl = () => {
  const possibleEnvVars = [
    process.env.NEON_NEON_NEON_NEON_DATABASE_URL,
    process.env.DATABASE_URL,
    process.env.NEON_DATABASE_URL,
    process.env.NEON_NEON_DATABASE_URL,
    process.env.NEON_NEON_NEON_DATABASE_URL,
    process.env.POSTGRES_URL,
  ]

  // Debug: Log all environment variables we're checking (without their values for security)
  console.log(
    "Checking for database URLs:",
    possibleEnvVars.map((url) => (url ? "[FOUND]" : "[NOT FOUND]")),
  )

  const url = possibleEnvVars.find((url) => url && url.length > 0)

  if (!url) {
    console.error("No database URL found in environment variables. Enabling demo mode.")
    // Instead of throwing an error, we'll return a flag indicating we should use demo mode
    return null
  }

  return url
}

// Flag to track if we're in demo mode
let isDemoMode = false

// Create the SQL client or set demo mode
const createSqlClient = () => {
  const url = getDatabaseUrl()

  if (!url) {
    isDemoMode = true
    // Return a mock SQL function that will be used in demo mode
    return () => Promise.resolve([])
  }

  return neon(url)
}

// Create the SQL client
export const sql = createSqlClient()

// Export the demo mode flag
export const isInDemoMode = () => isDemoMode

// Helper function to check database connection
export async function checkDatabaseConnection() {
  if (isDemoMode) {
    console.log("Running in demo mode, skipping database connection check")
    return true
  }

  try {
    console.log("Checking database connection...")
    const result = await sql`SELECT 1 as connection_test`
    console.log("Database connection successful:", result)
    return result && result.length > 0
  } catch (error) {
    console.error("Database connection check failed:", error)
    isDemoMode = true
    return false
  }
}

// Check if tables exist
export async function checkTablesExist() {
  if (isDemoMode) {
    console.log("Running in demo mode, assuming tables don't exist")
    return {
      usersTableExists: false,
      messagesTableExists: false,
      allTablesExist: false,
    }
  }

  try {
    // Check if anonymous_users table exists
    const usersTableExists = await sql`
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public'
        AND table_name = 'anonymous_users'
      ) as exists
    `

    // Check if messages table exists
    const messagesTableExists = await sql`
      SELECT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public'
        AND table_name = 'messages'
      ) as exists
    `

    return {
      usersTableExists: usersTableExists[0]?.exists || false,
      messagesTableExists: messagesTableExists[0]?.exists || false,
      allTablesExist: (usersTableExists[0]?.exists && messagesTableExists[0]?.exists) || false,
    }
  } catch (error) {
    console.error("Error checking if tables exist:", error)
    isDemoMode = true
    return {
      usersTableExists: false,
      messagesTableExists: false,
      allTablesExist: false,
    }
  }
}

// Initialize database tables if they don't exist
export async function initializeDatabase() {
  if (isDemoMode) {
    console.log("Running in demo mode, skipping database initialization")
    return true
  }

  try {
    console.log("Initializing database tables...")

    // Check if tables already exist
    const { allTablesExist } = await checkTablesExist()

    if (allTablesExist) {
      console.log("Tables already exist, skipping initialization")
      return true
    }

    // Create anonymous_users table if it doesn't exist
    await sql`
      CREATE TABLE IF NOT EXISTS anonymous_users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        nickname VARCHAR(255) NOT NULL,
        avatar_seed VARCHAR(255) NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        last_seen_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
      )
    `

    // Create messages table if it doesn't exist
    await sql`
      CREATE TABLE IF NOT EXISTS messages (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        user_id UUID NOT NULL,
        content TEXT NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
      )
    `

    // Create indexes for better performance
    await sql`CREATE INDEX IF NOT EXISTS idx_messages_user_id ON messages(user_id)`
    await sql`CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at)`
    await sql`CREATE INDEX IF NOT EXISTS idx_anonymous_users_last_seen_at ON anonymous_users(last_seen_at)`

    console.log("Database tables initialized successfully")
    return true
  } catch (error: any) {
    console.error("Error initializing database tables:", error)
    isDemoMode = true

    // Check if the error is about duplicate types (which we can ignore)
    if (error.message && error.message.includes("duplicate key value violates unique constraint")) {
      console.log("Ignoring duplicate key error, tables may already exist")
      return true
    }

    return false
  }
}

// Demo data for when database is not available
const DEMO_USERS = [
  {
    id: "demo-1",
    nickname: "RetroUser42",
    avatar_seed: "seed1",
    created_at: new Date().toISOString(),
    last_seen_at: new Date().toISOString(),
  },
  {
    id: "demo-2",
    nickname: "PixelMaster",
    avatar_seed: "seed2",
    created_at: new Date().toISOString(),
    last_seen_at: new Date().toISOString(),
  },
  {
    id: "demo-3",
    nickname: "WinClassic95",
    avatar_seed: "seed3",
    created_at: new Date().toISOString(),
    last_seen_at: new Date().toISOString(),
  },
  {
    id: "demo-4",
    nickname: "CoolHacker",
    avatar_seed: "seed4",
    created_at: new Date().toISOString(),
    last_seen_at: new Date().toISOString(),
  },
]

const DEMO_MESSAGES = [
  {
    id: "msg-1",
    content: "Welcome to the PumpRoom chat!",
    created_at: new Date(Date.now() - 3600000).toISOString(),
    user_id: "demo-1",
    anonymous_users: {
      nickname: "RetroUser42",
      avatar_seed: "seed1",
    },
  },
  {
    id: "msg-2",
    content: "This is a demo mode since no database is connected.",
    created_at: new Date(Date.now() - 3000000).toISOString(),
    user_id: "demo-2",
    anonymous_users: {
      nickname: "PixelMaster",
      avatar_seed: "seed2",
    },
  },
  {
    id: "msg-3",
    content: "You can still try out the chat interface!",
    created_at: new Date(Date.now() - 2400000).toISOString(),
    user_id: "demo-3",
    anonymous_users: {
      nickname: "WinClassic95",
      avatar_seed: "seed3",
    },
  },
  {
    id: "msg-4",
    content: "Messages in demo mode won't be saved permanently.",
    created_at: new Date(Date.now() - 1800000).toISOString(),
    user_id: "demo-4",
    anonymous_users: {
      nickname: "CoolHacker",
      avatar_seed: "seed4",
    },
  },
]

// In-memory storage for demo mode
const demoStorage = {
  users: [...DEMO_USERS],
  messages: [...DEMO_MESSAGES],
  lastId: 5,
}

// Helper function to get online users
export async function getOnlineUsers() {
  if (isDemoMode) {
    console.log("Running in demo mode, returning demo users")
    return demoStorage.users.filter(() => Math.random() > 0.3) // Randomly show some users as offline
  }

  try {
    // First check if the database connection is working
    const connectionOk = await checkDatabaseConnection()
    if (!connectionOk) {
      isDemoMode = true
      return demoStorage.users.filter(() => Math.random() > 0.3)
    }

    // Check if tables exist
    const { usersTableExists } = await checkTablesExist()

    if (!usersTableExists) {
      // Try to initialize the database
      const initialized = await initializeDatabase()
      if (!initialized) {
        isDemoMode = true
        return demoStorage.users.filter(() => Math.random() > 0.3)
      }
    }

    // Use a longer timeframe (30 minutes) to show more users
    const thirtyMinutesAgo = new Date(Date.now() - 30 * 60 * 1000).toISOString()

    console.log("Fetching online users since:", thirtyMinutesAgo)

    const users = await sql`
      SELECT * FROM anonymous_users 
      WHERE last_seen_at > ${thirtyMinutesAgo}
      ORDER BY nickname
      LIMIT 20
    `

    console.log(`Found ${users.length} online users`)
    return users
  } catch (error) {
    console.error("Error fetching online users:", error)
    isDemoMode = true
    return demoStorage.users.filter(() => Math.random() > 0.3)
  }
}

// Helper function to update user's last seen timestamp
export async function updateUserLastSeen(userId: string) {
  if (isDemoMode) {
    const user = demoStorage.users.find((u) => u.id === userId)
    if (user) {
      user.last_seen_at = new Date().toISOString()
    }
    return true
  }

  try {
    // Check if tables exist
    const { usersTableExists } = await checkTablesExist()

    if (!usersTableExists) {
      // Try to initialize the database
      const initialized = await initializeDatabase()
      if (!initialized) {
        isDemoMode = true
        return false
      }
    }

    await sql`
      UPDATE anonymous_users 
      SET last_seen_at = CURRENT_TIMESTAMP 
      WHERE id = ${userId}
    `
    return true
  } catch (error) {
    console.error("Error updating user last seen:", error)
    isDemoMode = true
    return false
  }
}

// Helper function to create a new anonymous user
export async function createAnonymousUser(nickname: string, avatarSeed: string) {
  if (isDemoMode) {
    const newId = `demo-${demoStorage.lastId++}`
    const newUser = {
      id: newId,
      nickname,
      avatar_seed: avatarSeed,
      created_at: new Date().toISOString(),
      last_seen_at: new Date().toISOString(),
    }
    demoStorage.users.push(newUser)
    return newUser
  }

  try {
    console.log("Creating user in database:", { nickname, avatarSeed })

    // Check if the database connection is working
    const connectionOk = await checkDatabaseConnection()
    if (!connectionOk) {
      isDemoMode = true
      return createAnonymousUser(nickname, avatarSeed) // Recursively call with demo mode enabled
    }

    // Check if tables exist
    const { usersTableExists } = await checkTablesExist()

    if (!usersTableExists) {
      // Try to initialize the database
      const initialized = await initializeDatabase()
      if (!initialized) {
        isDemoMode = true
        return createAnonymousUser(nickname, avatarSeed) // Recursively call with demo mode enabled
      }
    }

    const result = await sql`
      INSERT INTO anonymous_users (nickname, avatar_seed)
      VALUES (${nickname}, ${avatarSeed})
      RETURNING id, nickname, avatar_seed, created_at, last_seen_at
    `

    console.log("Database result:", result)

    if (!result || result.length === 0) {
      throw new Error("No user returned from database")
    }

    return result[0]
  } catch (error) {
    console.error("Database error creating anonymous user:", error)
    isDemoMode = true
    return createAnonymousUser(nickname, avatarSeed) // Recursively call with demo mode enabled
  }
}

// Helper function to get messages
export async function getMessages(limit = 100) {
  if (isDemoMode) {
    console.log("Running in demo mode, returning demo messages")
    return demoStorage.messages
  }

  try {
    // First check if the database connection is working
    const connectionOk = await checkDatabaseConnection()
    if (!connectionOk) {
      isDemoMode = true
      return demoStorage.messages
    }

    // Check if tables exist
    const { allTablesExist } = await checkTablesExist()

    if (!allTablesExist) {
      // Try to initialize the database
      const initialized = await initializeDatabase()
      if (!initialized) {
        isDemoMode = true
        return demoStorage.messages
      }
    }

    const messages = await sql`
      SELECT m.id, m.content, m.created_at, m.user_id, 
             u.nickname, u.avatar_seed
      FROM messages m
      JOIN anonymous_users u ON m.user_id = u.id
      ORDER BY m.created_at ASC
      LIMIT ${limit}
    `

    // Format the messages to match the expected structure
    return messages.map((msg) => ({
      id: msg.id,
      content: msg.content,
      created_at: msg.created_at,
      user_id: msg.user_id,
      anonymous_users: {
        nickname: msg.nickname,
        avatar_seed: msg.avatar_seed,
      },
    }))
  } catch (error) {
    console.error("Error fetching messages:", error)
    isDemoMode = true
    return demoStorage.messages
  }
}

// Helper function to get messages after a specific ID
export async function getNewMessages(lastMessageId: string) {
  if (isDemoMode) {
    // In demo mode, return any messages that were added after the last one the client has
    const lastMessageIndex = demoStorage.messages.findIndex((m) => m.id === lastMessageId)
    if (lastMessageIndex === -1) return []
    return demoStorage.messages.slice(lastMessageIndex + 1)
  }

  try {
    // Check if tables exist
    const { allTablesExist } = await checkTablesExist()

    if (!allTablesExist) {
      // Try to initialize the database
      const initialized = await initializeDatabase()
      if (!initialized) {
        isDemoMode = true
        return []
      }
    }

    const lastMessage = await sql`
      SELECT created_at FROM messages WHERE id = ${lastMessageId}
    `

    if (!lastMessage || lastMessage.length === 0) {
      return []
    }

    const lastTimestamp = lastMessage[0].created_at

    const messages = await sql`
      SELECT m.id, m.content, m.created_at, m.user_id, 
             u.nickname, u.avatar_seed
      FROM messages m
      JOIN anonymous_users u ON m.user_id = u.id
      WHERE m.created_at > ${lastTimestamp}
      ORDER BY m.created_at ASC
    `

    // Format the messages to match the expected structure
    return messages.map((msg) => ({
      id: msg.id,
      content: msg.content,
      created_at: msg.created_at,
      user_id: msg.user_id,
      anonymous_users: {
        nickname: msg.nickname,
        avatar_seed: msg.avatar_seed,
      },
    }))
  } catch (error) {
    console.error("Error fetching new messages:", error)
    isDemoMode = true
    return []
  }
}

// Helper function to create a new message
export async function createMessage(userId: string, content: string) {
  if (isDemoMode) {
    const newId = `msg-${demoStorage.lastId++}`
    const user = demoStorage.users.find((u) => u.id === userId)

    if (!user) {
      throw new Error("User not found in demo storage")
    }

    const newMessage = {
      id: newId,
      content,
      created_at: new Date().toISOString(),
      user_id: userId,
      anonymous_users: {
        nickname: user.nickname,
        avatar_seed: user.avatar_seed,
      },
    }

    demoStorage.messages.push(newMessage)
    return {
      id: newId,
      content,
      created_at: new Date().toISOString(),
      user_id: userId,
    }
  }

  try {
    // Check if tables exist
    const { allTablesExist } = await checkTablesExist()

    if (!allTablesExist) {
      // Try to initialize the database
      const initialized = await initializeDatabase()
      if (!initialized) {
        isDemoMode = true
        return createMessage(userId, content) // Recursively call with demo mode enabled
      }
    }

    const message = await sql`
      INSERT INTO messages (user_id, content)
      VALUES (${userId}, ${content})
      RETURNING id, content, created_at, user_id
    `

    // Also update the user's last seen timestamp
    await updateUserLastSeen(userId)

    return message[0]
  } catch (error) {
    console.error("Error creating message:", error)
    isDemoMode = true
    return createMessage(userId, content) // Recursively call with demo mode enabled
  }
}

// Helper function to get a message by ID
export async function getMessageById(messageId: string) {
  if (isDemoMode) {
    const message = demoStorage.messages.find((m) => m.id === messageId)
    return message || null
  }

  try {
    // Check if tables exist
    const { allTablesExist } = await checkTablesExist()

    if (!allTablesExist) {
      // Try to initialize the database
      const initialized = await initializeDatabase()
      if (!initialized) {
        isDemoMode = true
        return null
      }
    }

    const messages = await sql`
      SELECT m.id, m.content, m.created_at, m.user_id, 
             u.nickname, u.avatar_seed
      FROM messages m
      JOIN anonymous_users u ON m.user_id = u.id
      WHERE m.id = ${messageId}
    `

    if (!messages || messages.length === 0) {
      return null
    }

    const msg = messages[0]

    // Format the message to match the expected structure
    return {
      id: msg.id,
      content: msg.content,
      created_at: msg.created_at,
      user_id: msg.user_id,
      anonymous_users: {
        nickname: msg.nickname,
        avatar_seed: msg.avatar_seed,
      },
    }
  } catch (error) {
    console.error("Error fetching message by ID:", error)
    isDemoMode = true
    return null
  }
}
